# 数据汇总
import os
import traceback
from collections import defaultdict
from datetime import datetime
from pathlib import Path

import pandas as pd
from openpyxl.reader.excel import load_workbook
from openpyxl.styles import PatternFill

suffix_list = ['xlsx', 'xls', 'csv']
cur_dir = Path(__file__).resolve().parent
template_file_path = os.path.join(cur_dir, 'statics/考勤模板表.xlsx')
to_file_path = os.path.join(cur_dir, 'statics/考勤6月.xlsx')
data_file_path = os.path.join(cur_dir, 'statics/month6')
datetime_format = "%Y-%m-%d %H:%M:%S"
city_client_map = {
    '成都': '成都宏科',
    '上海': '皮尔博格',
    '宣城': '广德皮尔博格',
    '台州': '银轮',
    '承德': '承德苏垦银河',
}


class DataGather(object):
    def __init__(self):
        self.hangzhou_title = '浙江力太工业互联网有限公司'
        self.wuhan_title = '杭州数亦有道科技有限公司'
        self.sign_in_mark = '签到报表'
        self.clock_in_mark = '考勤报表'
        self.work_time_list = [('08:30:00', '17:30:00'), ('09:00:00', '18:00:00')]
        self.key_map = {
            self.hangzhou_title: 'hz',
            self.wuhan_title: 'wh',
        }
        self.cache_dict = {}

    def data_handle(self):
        for file in os.listdir(data_file_path):
            print('file name:', file)
            suffix = file.split('.')[-1]
            if suffix not in suffix_list:
                continue
            company, check_type, time_range, _ = file.replace('_', ' ').replace('.', ' ').split(
                ' ')
            file_path = os.path.join(data_file_path, file)
            key = self.key_map.get(company)
            self.cache_data(key, file_path, check_type, time_range)
        hz_clock_in = self.gather_table_data(self.cache_dict.get('hz'))
        wh_clock_in = self.gather_table_data(self.cache_dict.get('wh'))
        result = {**hz_clock_in, **wh_clock_in}
        self.write_check_in_excel(result)

    @staticmethod
    def write_check_in_excel(user_record_dict):
        book = load_workbook(template_file_path)
        # 创建填充对象，设置背景颜色为红色
        # fill = PatternFill(start_color='FFFF0000', end_color='FFFF0000', fill_type='solid')
        for sheet in book.worksheets:
            for i in range(6, sheet.max_row + 1):
                name_index = 2
                name = sheet.cell(row=i, column=name_index).value
                user_check: dict = user_record_dict.get(name, {})
                for day, r in user_check.items():
                    cell = sheet.cell(row=i, column=int(day) + name_index)
                    cell.value = r

        book.save(to_file_path)

    @staticmethod
    def gather_table_data(data):
        # 合成签到表和打卡表
        clock_in = data.get('考勤报表')
        sign_in = data.get('签到报表')
        for user, clock_record in clock_in.items():
            if not sign_in.get(user):
                continue
            # 两张表数据融合
            sign_user = sign_in.get(user)
            for d, r in clock_record.items():
                r_ = sign_user.get(d)
                if r in ['旷工', '休息']:
                    sign_r = r_
                    if sign_r:
                        if '正常' in sign_r:
                            clock_record[d] = sign_r.split('-')[1]
                        else:
                            clock_record[d] = sign_r
            clock_in[user] = clock_record
        for user, clock_record in clock_in.items():
            for d, r in clock_record.items():
                if r in ['正常', '休息']:
                    clock_record[d] = ''
        all_users = list(clock_in.keys())
        for user, sign_record in sign_in.items():
            if user not in all_users:
                clock_in[user] = sign_record.split('-')[-1]
        return clock_in

    def cache_data(self, key: str, file_path: str, check_type: str, time_range: str):
        cache_map = self.cache_dict.get(key) or {}
        data = self.check_type_map().get(check_type)(file_path, time_range)
        cache_map[check_type] = data
        self.cache_dict[key] = cache_map

    def sign_in_data(self, file_path: str, time_range: str):
        df = pd.read_excel(file_path, header=1, skiprows=1, engine='openpyxl')
        grouped = df.groupby(['日期', '姓名'])
        name_date_dict = defaultdict(dict)
        for name_date, group in grouped:
            city = group['详细地址']
            for k, v in city.items():
                if '市' in v:
                    city = v
                    break
            client = ''
            try:
                for k in city_client_map.keys():
                    if k in city:
                        client = city_client_map.get(k)
                        break
                else:
                    client = city.split('市')[0][-2:]
            except:
                print(traceback.format_exc())
            sign_time_list = [i for i in group['时间']]
            cur_date, name = name_date[0], name_date[1].replace(' ', '')
            day = cur_date.split('-')[-1]
            if not sign_time_list:
                sign_in_result = '旷工'
                name_date_dict[name][day] = sign_in_result
                continue
            in_str, out_str = min(sign_time_list), max(sign_time_list)  # 签到多次的情况
            #
            sign_in_result = self.get_sign_in_result(cur_date, in_str, out_str)
            sign_in_result_str = sign_in_result + '-' + client
            name_date_dict[name][day] = sign_in_result_str
        return name_date_dict

    def get_sign_in_result(self, cur_date, in_str, out_str):
        sign_in_result = ''

        wt_range_one, wt_range_two = self.work_time_list
        if in_str == out_str:
            wt_in = in_str
            if wt_in < '12:00:00':
                sign_in_result = '下午缺卡'
            elif wt_in > '12:00:00':
                sign_in_result = '上午缺卡'
            return sign_in_result

        cur_t_in, cur_t_out = self.to_datetime(cur_date, in_str), self.to_datetime(cur_date, out_str)
        wt_in_1, wt_out_1 = self.to_datetime(cur_date, wt_range_one[0]), self.to_datetime(cur_date, wt_range_one[1])
        wt_in_2, wt_out_2 = self.to_datetime(cur_date, wt_range_two[0]), self.to_datetime(cur_date, wt_range_two[1])
        weekday = wt_in_1.weekday()
        if weekday in [5, 6]:
            over_hour = round((cur_t_out - cur_t_in).total_seconds() / 3600, 1)
            sign_in_result = f'加班{over_hour}小时'
            return sign_in_result
        if any([cur_t_in <= wt_in_1 and cur_t_out >= wt_out_1, cur_t_in <= wt_in_2 and cur_t_out >= wt_out_2]):
            sign_in_result = '正常'
        elif cur_t_in > wt_in_2:
            # 9点以后来算迟到
            diff = round((cur_t_in - wt_in_2).total_seconds() // 60)
            sign_in_result = f'迟到{diff}min'
        elif cur_t_out < wt_out_1:
            # 17:30之前走,算早退
            diff = round((wt_out_2 - cur_t_out).total_seconds() // 60)
            sign_in_result = f'早退{diff}min'
        return sign_in_result

    @staticmethod
    def clock_in_data(file_path: str, time_range: str):
        df = pd.read_excel(file_path, header=1, skiprows=1, engine='openpyxl')
        date_s, date_e = time_range.split('-')
        day_s, day_e = int(date_s[-2:]), int(date_e[-2:])
        # 存在隐藏行:UserId
        name_date_dict = {}
        for index, row in df.iterrows():
            # '出勤天数':5,'休息天数':6,'旷工迟到天数':7,跳过这部分人员,从签到表检索
            if not row[5] and not row[6] and not row[7]:
                continue
            date_start_index = 8
            name = row['姓名'].replace(' ', '')
            name_date_dict[name] = {str(d).zfill(2): row[date_start_index + d - day_s] for d in range(day_s, day_e + 1)}
        return name_date_dict

    def check_type_map(self):
        return {
            self.sign_in_mark: self.sign_in_data,
            self.clock_in_mark: self.clock_in_data,
        }

    @staticmethod
    def to_datetime(date: str, time: str):
        datetime_str = date + ' ' + time
        return datetime.strptime(datetime_str, datetime_format)


if __name__ == '__main__':
    cls = DataGather()
    cls.data_handle()
